CREATE TABLE [dbo].[PromotionPush](
	[PromotionId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[DatePush] [datetime] NOT NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
 CONSTRAINT [PK_PromotionPush] PRIMARY KEY CLUSTERED 
(
	[PromotionId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PromotionPush]  WITH CHECK ADD  CONSTRAINT [FK_PromotionPush_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionPush] CHECK CONSTRAINT [FK_PromotionPush_Promotion]
GO
ALTER TABLE [dbo].[PromotionPush]  WITH CHECK ADD  CONSTRAINT [FK_PromotionPush_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionPush] CHECK CONSTRAINT [FK_PromotionPush_User]
GO
CREATE PROCEDURE spAPI_Promotion_Push
(
	@Token VARCHAR(255),
	@Lat FLOAT,
	@Long FLOAT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	BEGIN
		CREATE TABLE #tmpPromotions
		(
			PromotionId INT
		)
		
		BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId)
			SELECT Promotion.PromotionId
			FROM Promotion LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND (dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				AND (dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
			
			SELECT @Result = (SELECT Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions INNER JOIN Promotion ON #tmpPromotions.PromotionId = Promotion.PromotionId
											LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, AVG(CONVERT(FLOAT, Rating)) AS AvgRating 
													FROM BusinessReview
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							FOR XML PATH ('Business'), ROOT('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude)
			SELECT PromotionId, @UserId, GETDATE(), @Lat, @Long
			FROM #tmpPromotions
			
		COMMIT TRANSACTION
		
		DROP TABLE #tmpPromotions
	END
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO


